package dao.impl.CourseDaoImpl;

import common.model.Entity.courseEntity.CourseLocation;
import dao.CourseDao.CourseLocationDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import common.util.DBUtil;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

/**
 * CourseLocationDao 接口的实现类，用于操作教室位置信息表 t_course_location。
 * 提供增删改查、按教学楼或教室号查询、统计数量等功能。
 */
public class CourseLocationDaoImpl implements CourseLocationDao {

    private final QueryRunner queryRunner = new QueryRunner();

    /**
     * 根据ID查询教室位置信息。
     *
     * @param id 教室位置ID
     * @return 返回包含教室位置的 Optional，如果不存在返回 Optional.empty()
     */
    @Override
    public Optional<CourseLocation> findById(Long id) {
        String sql = """
            SELECT id, building, room_number AS roomNumber,
                   created_at AS createdAt, updated_at AS updatedAt, is_deleted AS isDeleted
            FROM t_course_location
            WHERE id = ? AND is_deleted = 0
            """;

        try (Connection conn = DBUtil.getConnection()) {
            CourseLocation location = queryRunner.query(conn, sql,
                    new BeanHandler<>(CourseLocation.class), id);
            return Optional.ofNullable(location);
        } catch (SQLException e) {
            throw new RuntimeException("根据ID查询教室位置失败: id=" + id, e);
        }
    }

    /**
     * 查询所有未删除的教室位置。
     *
     * @return 返回教室位置列表
     */
    @Override
    public List<CourseLocation> findAll() {
        String sql = """
            SELECT id, building, room_number AS roomNumber,
                   created_at AS createdAt, updated_at AS updatedAt, is_deleted AS isDeleted
            FROM t_course_location
            WHERE is_deleted = 0
            ORDER BY building, room_number
            """;

        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(CourseLocation.class));
        } catch (SQLException e) {
            throw new RuntimeException("查询所有教室位置失败", e);
        }
    }

    /**
     * 保存新的教室位置。
     *
     * @param entity 教室位置实体
     * @return 保存成功返回 true，否则 false
     */
    @Override
    public boolean save(CourseLocation entity) {
        String sql = """
            INSERT INTO t_course_location (building, room_number)
            VALUES (?, ?)
            """;

        try (Connection conn = DBUtil.getConnection()) {
            int affectedRows = queryRunner.update(conn, sql,
                    entity.getBuilding(),
                    entity.getRoomNumber()
            );

            if (affectedRows > 0) {
                Object generatedIdObj = queryRunner.query(conn,
                        "SELECT LAST_INSERT_ID()", new ScalarHandler<>());

                Long generatedId;
                if (generatedIdObj instanceof BigInteger) {
                    generatedId = ((BigInteger) generatedIdObj).longValue();
                } else if (generatedIdObj instanceof Number) {
                    generatedId = ((Number) generatedIdObj).longValue();
                } else {
                    throw new RuntimeException("无法处理的自增ID类型: " + generatedIdObj.getClass());
                }

                entity.setId(generatedId);
                return true;
            }
            return false;
        } catch (SQLException e) {
            throw new RuntimeException("保存教室位置失败: " + entity, e);
        }
    }

    /**
     * 更新教室位置信息。
     *
     * @param entity 教室位置实体
     * @return 更新成功返回 true，否则 false
     */
    @Override
    public boolean update(CourseLocation entity) {
        Optional<CourseLocation> existing = findById(entity.getId());
        if (existing.isEmpty()) {
            throw new RuntimeException("教室位置不存在，ID: " + entity.getId());
        }

        String sql = """
            UPDATE t_course_location SET
                building = ?, room_number = ?, updated_at = CURRENT_TIMESTAMP
            WHERE id = ? AND is_deleted = 0
            """;

        try (Connection conn = DBUtil.getConnection()) {
            int affectedRows = queryRunner.update(conn, sql,
                    entity.getBuilding(),
                    entity.getRoomNumber(),
                    entity.getId()
            );
            return affectedRows > 0;
        } catch (SQLException e) {
            throw new RuntimeException("更新教室位置失败: " + entity, e);
        }
    }

    /**
     * 根据ID逻辑删除教室位置。
     *
     * @param id 教室位置ID
     * @return 删除成功返回 true，否则 false
     */
    @Override
    public boolean deleteById(Long id) {
        String sql = """
            UPDATE t_course_location 
            SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP
            WHERE id = ? AND is_deleted = 0
            """;

        try (Connection conn = DBUtil.getConnection()) {
            int affectedRows = queryRunner.update(conn, sql, id);
            return affectedRows > 0;
        } catch (SQLException e) {
            throw new RuntimeException("删除教室位置失败: id=" + id, e);
        }
    }

    /**
     * 统计教室位置数量。
     *
     * @return 返回未删除教室位置的总数
     */
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM t_course_location WHERE is_deleted = 0";

        try (Connection conn = DBUtil.getConnection()) {
            Long count = queryRunner.query(conn, sql, new ScalarHandler<Long>());
            return count != null ? count : 0;
        } catch (SQLException e) {
            throw new RuntimeException("统计教室位置数量失败", e);
        }
    }

    /**
     * 根据教学楼查询教室位置列表。
     *
     * @param building 教学楼
     * @return 教室位置列表
     */
    @Override
    public List<CourseLocation> findByBuilding(String building) {
        String sql = """
            SELECT id, building, room_number AS roomNumber, 
                   created_at AS createdAt, updated_at AS updatedAt, is_deleted AS isDeleted
            FROM t_course_location 
            WHERE building = ? AND is_deleted = 0 
            ORDER BY room_number
            """;

        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql,
                    new BeanListHandler<>(CourseLocation.class), building);
        } catch (SQLException e) {
            throw new RuntimeException("根据教学楼查询教室位置失败: building=" + building, e);
        }
    }

    /**
     * 根据教室号查询教室位置列表。
     *
     * @param roomNumber 教室号
     * @return 教室位置列表
     */
    @Override
    public List<CourseLocation> findByRoomNumber(String roomNumber) {
        String sql = """
            SELECT id, building, room_number AS roomNumber, 
                   created_at AS createdAt, updated_at AS updatedAt, is_deleted AS isDeleted
            FROM t_course_location 
            WHERE room_number = ? AND is_deleted = 0
            """;

        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql,
                    new BeanListHandler<>(CourseLocation.class), roomNumber);
        } catch (SQLException e) {
            throw new RuntimeException("根据教室号查询教室位置失败: roomNumber=" + roomNumber, e);
        }
    }

    /**
     * 根据教学楼和教室号查询单个教室位置。
     *
     * @param building 教学楼
     * @param roomNumber 教室号
     * @return 返回包含教室位置的 Optional，如果不存在返回 Optional.empty()
     */
    @Override
    public Optional<CourseLocation> findByBuildingAndRoomNumber(String building, String roomNumber) {
        String sql = """
            SELECT id, building, room_number AS roomNumber,
                   created_at AS createdAt, updated_at AS updatedAt, is_deleted AS isDeleted
            FROM t_course_location
            WHERE building = ? AND room_number = ? AND is_deleted = 0
            """;

        try (Connection conn = DBUtil.getConnection()) {
            CourseLocation location = queryRunner.query(conn, sql,
                    new BeanHandler<>(CourseLocation.class), building, roomNumber);
            return Optional.ofNullable(location);
        } catch (SQLException e) {
            throw new RuntimeException("根据教学楼和教室号查询失败: building=" + building + ", roomNumber=" + roomNumber, e);
        }
    }

    /**
     * 测试主方法，演示查询操作。
     *
     * @param args 命令行参数
     */
    public static void main(String[] args) {
        CourseLocation entity = new CourseLocation("教学楼1","102");
        CourseLocationDao courseLocationDao = new CourseLocationDaoImpl();
//        courseLocationDao.save(entity);
        List<CourseLocation> list = courseLocationDao.findByRoomNumber("101");
        System.out.println(list.size());
    }
}
